package cn.com.callback

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import cn.com.elite.EliteBean;
import cn.com.elite.Util
import org.json.JSONArray;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import cn.com.kh.getProperty;
import cn.com.kh.KHUtil
import org.json.JSONObject
import com.huasheng.passwordencrypt.DecryptUtil

/**
 *
 * @Title: apiHstGetEmailWorkorder* @Description: 定期扫描 邮件账号接收邮件，并判断是否启用规则生成工单 --内部接口
 * @author: chieh* @date 2023-11-01 下午15:30:48
 *
 */

public class apiHstGetEmailWorkorder implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiHstGetEmailWorkorder.class);
    private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ", Locale.CHINA);
    private static DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("UTF-8");

        String getMethod = request.getMethod();

        log.debug("getMethod:" + getMethod);
        long endTime;
        String inData = "";
        String outData = "";
        String dataSourceID = "dataSource";

        BufferedReader iny = null;
        if (getMethod.equals("GET")) {
            inData = request.getParameter("data");
            log.debug("原始Get请求参数:" + inData);
        } else {
            InputStreamReader reader = new InputStreamReader(request.getInputStream(), "UTF-8");
            iny = new BufferedReader(reader);
            String line;
            while ((line = iny.readLine()) != null) {
                inData += line;
            }
            log.debug("原始Post请求参数:" + inData);
        }

        String ipserver = "";//邮箱服务器
        String urladdress = "";//邮箱地址
        String sslport = "";//组邮箱地址
        String emailpassword = "";//邮箱密码
        String rulejson = "";//规则json
        String isnotworkorder = "";//是否创建工单 1是 0否
        String access_token = "";//access_token

        //检查token有效期 是否需要重新刷新
        Map<String, String> headMap = new HashMap<String, String>();
        headMap.put("Content-Type", "application/json");
        //获取配置参数
        String sysurltoken = getProperty.get("crmurl");//配置本地接口地址
        sysurltoken = sysurltoken + "apiSync_refresh_token";
        //华盛证券，自动刷新Esop邮件token
        Util.doHttpPostUseHead(sysurltoken, "{}", "UTF-8", 100000, 0, headMap);


        //附件路径临时日期的判断
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyyMMdd");
        String time = dateformat.format(System.currentTimeMillis());
        //检查接收邮件路径下文件夹是否存在
        String FILEURL = getProperty.get("EMAILPATH");//存储附件的路径 /nfs/ngsfiles/receiveemail/
        //检查每日生成的时间路径是否存在
        String FILEURLtime = FILEURL + time;

        //路径是否存在
        File filefolder = new File(FILEURLtime);
        //如果文件夹不存在则创建
        if (!filefolder.exists() && !filefolder.isDirectory()) {
            log.debug("//不存在");
            filefolder.mkdir();
        } else {
            log.debug("//目录存在");
        }

        List selectconfigList = eliteBean.chaxun_sql("select h.ipserver,h.urladdress,h.sslport,h.emailpassword,h.rulejson,h.isnotworkorder,h.access_token from hst_email_config h left join hst_email_parameter p on p.hst_guid=h.ipserver  where 1=1 and h.status=1 and h.rulejson is not null order by h.updateDate desc", "dataSource");
        def configcount = selectconfigList.size();
        log.debug("待处理的邮件配置数量 configcount：" + configcount);
        //判断如待处理的邮件配置数量大于0则执行
        for (int i = 0; i < configcount; i++) {
            try {
                Map selectbrandMap = selectconfigList.get(i);
                ipserver = selectbrandMap.get("ipserver");
                urladdress = selectbrandMap.get("urladdress");
                sslport = selectbrandMap.get("sslport");
                emailpassword = selectbrandMap.get("emailpassword");
                rulejson = selectbrandMap.get("rulejson");
                isnotworkorder = selectbrandMap.get("isnotworkorder");
                access_token = selectbrandMap.get("access_token");
                log.debug("解析当前规则配置：ipserver:" + ipserver + "	urladdress:" + urladdress + "	sslport:" + sslport + "	emailpassword:" + emailpassword + "	rulejson:" + rulejson + "	isnotworkorder:" + isnotworkorder + "	access_token:" + access_token);

                headMap.put("Authorization", "Bearer " + access_token);
                //获取配置参数 --默认获取最新100条  按接收时间 receivedDateTime 排序
                String sysurl = "https://graph.microsoft.com/v1.0/users/" + urladdress + "/messages?top=100&orderby=receivedDateTime%20desc";
                String messagesresult = Util.doHttpGetUseHead(sysurl, "UTF-8", 100000, 0, headMap);

                //解析返回邮箱内容信息
                JSONObject messagesresultObject = new JSONObject(messagesresult);
                String messagesvalue = GetJV(messagesresultObject, "value");
                //String nextLink = GetJV(messagesresultObject, "@odata.nextLink");
                //log.debug("value:" + messagesvalue);
                //log.debug("nextLink:" + nextLink);
                //log.debug("messagesvalue:" + messagesvalue);

                JSONArray valueArr = new JSONArray(messagesvalue);
                int valuelength = valueArr.length();
                log.debug("valuelength:" + valuelength);
                log.debug("------------------------" + urladdress + " 开始解析邮件----------------------------------");

                // 解析读取邮件
                for (int j = 0; j < valuelength; j++) {
                    log.debug("邮件次序：" + j);
                    //解析邮件内容
                    try {
                        //获取唯一id  检查 邮箱接收表内是否存在
                        JSONObject messagesvalueobj = valueArr.getJSONObject(j); // 获取每个元素（对象）
                        String id = messagesvalueobj.getString("id");//对话id作为唯一校验 id
                        Boolean hasAttachments = messagesvalueobj.getBoolean("hasAttachments");//是否有附件
                        String sentDateTime = messagesvalueobj.getString("sentDateTime");
                        log.debug("id:" + id);
                        log.debug("sentDateTime:" + sentDateTime);

                        //邮件发送时间 需要转换
                        //进行转化时区
                        Date sendDate = dateFormat.parse(sentDateTime.replace("Z", "+0000"));
                        log.debug("sendDate:" + sendDate);
                        //转换为年月日时分秒
                        sentDateTime = df.format(sendDate);
                        log.debug("sentDateTime 转化后:" + sentDateTime);

                        def getSelectidcountSQL = eliteBean.chaxun_sql("select count(*) as idcount from  documentin where  1=1 and stringfield1='" + id + "'", "dataSource");
                        def idcount = getSelectidcountSQL.get(0).get("idcount");
                        log.debug("当前邮件是否已存在:" + idcount);
                        if (idcount == 0) {

                            // 生成相关ID
                            String Temp_Guid = KHUtil.getRandomString(31).toUpperCase();
                            Temp_Guid = "EMAIL" + Temp_Guid;
                            log.debug("Temp_Guid:    " + Temp_Guid);

                            String absolutepath = FILEURLtime + "/" + Temp_Guid + ".eml";
                            log.debug("当前邮件eml存储绝对路径：" + absolutepath);// /nfs/ngsfiles/receiveemail/receiveemail/20230903/xxx.eml
                            String relativepath = "/receiveemail/" + time + "/" + Temp_Guid + ".eml";
                            log.debug("当前邮件eml存储documentin表内的相对路径：" + relativepath);// /receiveemail/20230903/xxx.eml

                            //邮件 主题
                            String subject = messagesvalueobj.getString("subject");
                            //处理异常的主题的转义 例如单引号 双引号
                            subject=subject.replaceAll("'","\'");
                            subject=subject.replaceAll("'","\"");

                            //接收时间
                            String receivedDateTime = messagesvalueobj.getString("receivedDateTime");
                            //邮件接收时间 需要转换
                            //进行转化时区
                            Date receivedDate = dateFormat.parse(receivedDateTime.replace("Z", "+0000"));
                            log.debug("receivedDate:" + receivedDate);
                            //转换为年月日时分秒
                            receivedDateTime = df.format(receivedDate);
                            log.debug("receivedDateTime 转化后:" + receivedDateTime);

                            String emailAddress;
                            String address;

                            String mailContent = messagesvalueobj.getJSONObject("body").getString("content");
                            String importance = messagesvalueobj.getString("importance");
                            //发件人
                            String fromStr = "";//发件人 --具体邮箱地址
                            JSONObject fromObject = messagesvalueobj.getJSONObject("from");

                            if (fromObject.has("emailAddress")) {
                                address = fromObject.getJSONObject("emailAddress").getString("address");
                                log.debug("发件人address" + address);
                                fromStr = address;
                            } else {
                                log.debug("发送人为空，当前邮箱" + urladdress + "解析异常跳出！");
                                continue;
                            }
                            log.debug("发送人 fromStr:" + fromStr);

                            if(!KHUtil.isNull(urladdress)){
                                //如果当前邮件的发件人也是本邮箱配置的邮箱,则跳出 不计入接收任务
                                if (fromStr.toLowerCase().equals(urladdress.toLowerCase())) {
                                    log.debug("发送人为当前邮箱" + fromStr + "解析异常跳出！");
                                    continue;
                                }
                            }

                            if(!KHUtil.isNull(sslport)){
                                //如果当前邮件的发件人也是本邮箱配置的组邮箱,则跳出 不计入接收任务
                                if (fromStr.toLowerCase().equals(sslport.toLowerCase())) {
                                    log.debug("发送人为当前组邮箱" + fromStr + "解析异常跳出！");
                                    continue;
                                }
                            }

                            //收件人
                            String toListStr = "";//收件人（多个按,隔开）--具体邮箱地址
                            JSONArray toRecipientsArr = messagesvalueobj.getJSONArray("toRecipients");
                            int toRecipientsArrlength = toRecipientsArr.length();
                            log.debug("toRecipientsArrlength:" + toRecipientsArrlength);
                            if (toRecipientsArrlength > 0) {
                                for (int n = 0; n < toRecipientsArrlength; n++) {
                                    JSONObject toRecipientsobj = toRecipientsArr.getJSONObject(n); // 获取每个元素（对象）
                                    address = toRecipientsobj.getJSONObject("emailAddress").getString("address");
                                    log.debug("收件人address" + n + ":" + address);
                                    toListStr = toListStr + "," + address;
                                }
                                //截去首位逗号
                                toListStr = toListStr.substring(1);
                            }
                            log.debug("收件人列表 toListStr:" + toListStr);

                            //抄送人
                            String ccListStr = "";//抄送人（多个按,隔开）--具体邮箱地址
                            JSONArray ccRecipientsArr = messagesvalueobj.getJSONArray("ccRecipients");
                            int ccRecipientslength = ccRecipientsArr.length();
                            log.debug("ccRecipientslength:" + ccRecipientslength);
                            if (ccRecipientslength > 0) {
                                for (int o = 0; o < ccRecipientslength; o++) {
                                    JSONObject ccRecipientsobj = ccRecipientsArr.getJSONObject(o); // 获取每个元素（对象）
                                    address = ccRecipientsobj.getJSONObject("emailAddress").getString("address");
                                    log.debug("抄送人address" + o + ":" + address);
                                    ccListStr = ccListStr + "," + address;
                                }
                                //截去首位逗号
                                ccListStr = ccListStr.substring(1);
                            }
                            log.debug("抄送人列表 ccListStr:" + ccListStr);

                            //列出当前邮件的正文 逻辑
                            String getMailEmlURL = "https://graph.microsoft.com/v1.0/me/messages/" + id + "/\$value";
                            String getMailEml = Util.doHttpGetUseHead(getMailEmlURL, "UTF-8", 100000, 0, headMap);
                            //解析返回邮箱MIME内容信息
                            Util.appendFileContent(absolutepath, getMailEml);

                            String fileflag = hasAttachments == true ? "1" : "0";
                            log.debug("当前邮件是否携带附件:" + fileflag);

                            //2.入库 收邮件表 documentin 接收记录
                            def insertdocumentinsql = "INSERT INTO documentin (DOCUMENTIN_GUID, DOCTYPE, status, documentpath, COMMENTS, documentfrom, documentto, RECEIVEDDATETIME, HANDLEBY_ID, HANDLEGROUP_ID, PRIORITY_ID, CUSTOMER_GUID, SUBSCRIBER_GUID, ACCOUNT_GUID, OBJECTIVE_GUID, MODIFIEDBY, MODIFIEDDATE, LOCKEDBY, POSTPROCESSED, STRINGFIELD1, STRINGFIELD2, REPLYMODE, FirstBy, FirstModifyDate, subject, CloseDate, FirstCloseBy, FirstCloseGroup, FirstCloseDate, ServerReceivedTime, DocumentCC, attachflag, indexflag, replyflag, SENDDATETIME,msgid) VALUES ('" + Temp_Guid + "', 2, 0, '" + relativepath + "', '" + subject + "', '" + fromStr + "', '" + toListStr + "', '" + receivedDateTime + "', 'SELITE', 'SYSTEM', '" + importance + "', NULL, NULL, NULL, NULL, 'SELITE', now(), NULL, NULL, '" + id + "', NULL, NULL, NULL, NULL, '" + subject + "', NULL, NULL, NULL, NULL, NULL, '" + ccListStr + "', '" + fileflag + "', '0', '0', '" + sentDateTime + "',uuid())";
                            log.debug("insertdocumentinsql:" + insertdocumentinsql);
                            def insertdocumentinresult = eliteBean.update(insertdocumentinsql, "dataSource");
                            log.debug("insertdocumentinresult:" + insertdocumentinresult);

                            //查询当前邮件的自增documentinno 对应 hst_workorderlist表 hst_sourceId
                            def selectsourceidSQL = "select documentinno from documentin  Where DOCUMENTIN_GUID = '" + Temp_Guid + "'";
                            def selectsourceidresult = eliteBean.chaxun_sql(selectsourceidSQL, "dataSource");
                            String sourceId = "";
                            if (selectsourceidresult.size() > 0) {
                                sourceId = selectsourceidresult.get(0).get("documentinno");
                            }
                            log.debug("sourceId:    " + sourceId);

                            //获取正文
                            mailContent = delHTMLTag(mailContent);
                            //log.debug("正文 去HTML+CSS标签:" + html);

                            //转义 &nbsp;&quot;&amp;&lt;&gt;
                            mailContent = mailContent.replaceAll("&amp;", "&");
                            mailContent = mailContent.replaceAll("&lt;", "<");
                            mailContent = mailContent.replaceAll("&gt;", ">");
                            mailContent = mailContent.replaceAll("&nbsp;", " ");
                            mailContent = mailContent.replaceAll("&quot;", "");
                            //log.debug("正文 去特殊字符串:" + html);
                            mailContent=subject+mailContent;//把邮件标题拼接到 正文内 方便识别规则

                            //3.isnotworkorder 判断当前邮件配置是否需要生成工单
                            //3.1无论是否需要 都检查工单编号
                            String regexzz = "GD-\\d{4}\\d{1,2}\\d{1,2}\\d{1,4}";
                            Pattern pattern = Pattern.compile(regexzz);
                            Matcher matcher = pattern.matcher(mailContent);

                            String workorderno = "";//工单id
                            String workorderlist = "";//工单多个in
                            int workorderorder = 0;
                            //3.2查找 邮件主题 subject 邮件内容 html 是否带有工单编号正则查找 GD-202311010013
                            //3.3工单编号的正则 GD-\d{4}\d{1,2}\d{1,2}\d{1,4}
                            while (matcher.find()) {
                                workorderno = matcher.group();
                                log.debug("匹配到的workorderno：" + workorderno);
                                if (workorderorder == 0) {
                                    workorderlist = workorderno;
                                } else {
                                    workorderlist = workorderlist + "','" + workorderno;
                                }
                                workorderorder = workorderorder + 1;//累计当前多少次
                            }
                            log.debug("匹配到的 workorderlist：" + workorderlist + " 累计次数workorderorder：" + workorderorder);

                            //3.4拿到匹配的工单编号，查询当前库内 最新的那一条匹配
                            def selectlinkguidSQL = "select link_guid,workorderstatus from hst_workorder where 1=1 and workorderno in ('" + workorderlist + "') order by createdate desc limit 1";
                            def selectlinkguidresult = eliteBean.chaxun_sql(selectlinkguidSQL, "dataSource");
                            String linkguid = "";//工单关联id
                            String workorderstatus = "";//OPEN 处理中 FAILED 失败结案 CLOSE 成功结案  STOP 作废
                            String createreason = "";//创建原因
                            if (selectlinkguidresult.size() > 0) {
                                linkguid = selectlinkguidresult.get(0).get("link_guid");
                                workorderstatus = selectlinkguidresult.get(0).get("workorderstatus");
                                log.debug("linkguid:    " + linkguid + " workorderstatus:    " + workorderstatus);
                                createreason = "workorderno";
                                //当前匹配到的工单是否属于处理中
                                if (workorderstatus == "OPEN") {//处理中
                                    log.debug("匹配到处理中的工单,获取老的工单的linkguid更新跳出");
                                    //3.5拿到对应的 link_guid 绑定关联工单 更新邮件表 documentin 归属的link_guid
                                    //不创建，直接关联工单还是老的工单的linkguid
                                    def updatedocumentinsql = "update documentin set link_guid ='" + linkguid + "' where 1=1 and DOCUMENTIN_GUID = '" + Temp_Guid + "' and documentfrom = '" + fromStr + "'";
                                    log.debug("updatedocumentinsql:" + updatedocumentinsql);
                                    def updatedocumentinresult = eliteBean.update(updatedocumentinsql, "dataSource");
                                    log.debug("updatedocumentinresult:" + updatedocumentinresult);
                                    //处理完毕跳出当前循环
                                    continue;
                                } else {//非处理中
                                    log.debug("继续匹配关键字，去创建新工单，但是关联工单还是老的工单的linkguid");
                                    //直接关联工单还是老的工单的linkguid
                                    def updatedocumentinsql = "update documentin set link_guid ='" + linkguid + "' where 1=1 and DOCUMENTIN_GUID = '" + Temp_Guid + "' and documentfrom = '" + fromStr + "'";
                                    log.debug("updatedocumentinsql:" + updatedocumentinsql);
                                    def updatedocumentinresult = eliteBean.update(updatedocumentinsql, "dataSource");
                                    log.debug("updatedocumentinresult:" + updatedocumentinresult);
                                }
                            } else {
                                // 未查到关联的工单则，生成新的 linkguid
                                linkguid = KHUtil.getRandomString(31).toUpperCase();
                                linkguid = "LINK_" + linkguid;
                                log.debug("linkguid:    " + linkguid);
                                //关联工单的linkguid
                                def updatedocumentinsql = "update documentin set link_guid ='" + linkguid + "' where 1=1 and DOCUMENTIN_GUID = '" + Temp_Guid + "' and documentfrom = '" + fromStr + "'";
                                log.debug("updatedocumentinsql:" + updatedocumentinsql);
                                def updatedocumentinresult = eliteBean.update(updatedocumentinsql, "dataSource");
                                log.debug("updatedocumentinresult:" + updatedocumentinresult);
                            }
                            log.debug("linkguid:    " + linkguid);

                            //3.6hst_workorderlist 表
                            // 创建原因 hst_createreason 匹配工单编号创建 workorderno
                            // 反馈来源ID  hst_sourceId 邮件ID
                            if (isnotworkorder == "1") {//是
                                log.debug("配置 需要创建工单!");

                                //4.for循环 在发送人 标题 内容 中查找 当前配置规则关键字
                                JSONObject inObject = new JSONObject(rulejson);
                                String weight_json = GetJV(inObject, "rulejson");

                                JSONArray arrJsonArray = new JSONArray(weight_json);
                                int arrInt = arrJsonArray.length();
                                log.debug("接入List规则:" + arrInt);

                                for (int k = 0; k < arrInt; k++) {
                                    JSONObject arrJson = arrJsonArray.getJSONObject(k);
                                    //获取信息
                                    String rule = GetJV(arrJson, "rule");//首条规则
                                    String node_guid = GetJV(arrJson, "node_guid");//工单流程ID
                                    String workordertype1 = GetJV(arrJson, "workordertype1");//业务类型一级
                                    String workordertype2 = GetJV(arrJson, "workordertype2");//业务类型二级
                                    String workordertype3 = GetJV(arrJson, "workordertype3");//业务类型三级
                                    log.debug("接入rule:" + rule);
                                    log.debug("接入node_guid:" + node_guid);
                                    log.debug("接入nworkordertype1:" + workordertype1);
                                    log.debug("接入workordertype2:" + workordertype2);
                                    log.debug("接入workordertype3:" + workordertype3);

                                    boolean createType;
                                    //首条规则+发送人+邮件主题 判断是否匹配当前邮件内容生成工单
                                    if (mailContent.indexOf(rule) == -1 && fromStr.indexOf(rule) == -1 && subject.indexOf(rule) == -1) {
                                        log.debug("首条规则在 邮件正文+发送人+邮件主题  不包含关键字，跳出!");
                                    } else {
                                        log.debug("首条规则在 邮件正文+发送人+邮件主题 包含关键字!" + rule);
                                        if (createreason == "workorderno") {
                                            //匹配工单号情况下，名字关键字规则创建
                                        } else {
                                            createreason = "keywords";
                                        }
                                        createType = workOrderCreate(eliteBean, linkguid, workordertype1, workordertype2, workordertype3, sourceId, createreason, fromStr, Temp_Guid);
                                        log.debug("根据首条规则匹配创建工单情况!" + createType);
                                        continue;//根据首条规则命中后 ，那么细则就无需继续匹配了
                                    }

                                    String justrule = GetJV(arrJson, "justrule");//其他规则
                                    log.debug("接入justrule:" + justrule);
                                    JSONArray justruleJsonArray = new JSONArray(justrule);
                                    int justruleInt = justruleJsonArray.length();
                                    log.debug("接入List细则:" + justruleInt);
                                    for (int l = 0; l < justruleInt; l++) {
                                        JSONObject justruleJson = justruleJsonArray.getJSONObject(l);
                                        String just = GetJV(justruleJson, "just");//细则规则
                                        log.debug(l + "接入just:" + just);
                                        //细则规则判断是否匹配当前邮件内容生成工单
                                        //首条规则+发送人+邮件主题 判断是否匹配当前邮件内容生成工单
                                        if (mailContent.indexOf(just) == -1 && fromStr.indexOf(just) == -1 && subject.indexOf(just) == -1) {
                                            log.debug("细则规则在 邮件正文+发送人+邮件主题  不包含关键字，跳出!");
                                        } else {
                                            log.debug("细则规则在 邮件正文+发送人+邮件主题 包含关键字!" + just);
                                            if (createreason == "workorderno") {
                                                //匹配工单号情况下，名字关键字规则创建
                                            } else {
                                                createreason = "keywords";
                                            }
                                            createType = workOrderCreate(eliteBean, linkguid, workordertype1, workordertype2, workordertype3, sourceId, createreason, fromStr, Temp_Guid);
                                            log.debug("根据细则规则匹配创建工单情况!" + createType);
                                            break;//只要命中一次就会 直接跳出 匹配下一层规则
                                        }
                                    }

                                }

                            } else {//否
                                log.debug("配置 不需要创建工单!");
                            }

                        }
                    } catch (Exception e) {
                        log.debug("解析邮件内容异常跳出!" + e.getMessage());
                    }
                }
                log.debug("邮件账号循环读取完成！");

            } catch (Exception e) {
                log.error(urladdress + " 当前邮件处理异常:" + e.getMessage());
            }
        }

        outData = "{\"resultcode\":\"1\",\"resultmessage\":\"处理成功\",\"data\":[]}";
        log.debug("返回结果:" + outData);
        return outData;
    }

    /**
     * 创建工单
     * @param eliteBean
     * @return 成功 失败
     */
    public static boolean workOrderCreate(EliteBean eliteBean, String linkguid, String workordertype1, String workordertype2, String workordertype3, String sourceId, String createreason, String from, String Temp_Guid) {
        boolean reFlag = true;

        String from_enc = DecryptUtil.encrypt(from);
        log.debug("发送人邮箱 from_enc:" + from_enc);
        String newCustomer_Guid;

        try {
            //根据发件人 from 获取customer_guid
            List custCountList = eliteBean.chaxun_sql("Select Customer_Guid,ifnull(customer_id,'') as customer_id,ifnull(customername,'') as customername,ifnull(mobile,'') as mobile,ifnull(mobile_enc,'') as mobile_enc,ifnull(gender,'') as gender From Customer where 1=1 and stringfield105='Esop' and E_MAIL_enc = '" + from_enc + "' order by MODIFIEDDATE desc limit 1 ", "dataSource");
            String Customer_Guid = "";
            String customer_id = "";
            String customername = "";
            String mobile = "";
            String mobile_enc = "";
            String gender = "";
            if (custCountList.size() > 0) {
                Customer_Guid = custCountList.get(0).get("Customer_Guid");
                customer_id = custCountList.get(0).get("customer_id");
                customername = custCountList.get(0).get("customername");
                mobile = custCountList.get(0).get("mobile");
                mobile_enc = custCountList.get(0).get("mobile_enc");
                gender = custCountList.get(0).get("gender");
                log.debug("客户customer_id：" + Customer_Guid + "    customer_id：" + customer_id + " customername：" + customername + " mobile：" + mobile + " mobile_enc：" + mobile_enc + " gender：" + gender);
            } else {
                // 生成相关ID
                String new_Guid = KHUtil.getRandomString(31).toUpperCase();
                newCustomer_Guid = "Esop_" + new_Guid;

                def insertnewcustSQL = "insert into customer(customer_guid,customername,stringfield105,E_MAIL,E_MAIL_enc,CREATEDBY,CREATEDDATE) select '" + newCustomer_Guid + "','Esop客户','Esop','***','" + from_enc + "','SELITE',NOW()";
                def insertnewcustSQLresult = eliteBean.update(insertnewcustSQL, "dataSource");
                log.debug("insertnewcustSQLresult:    " + insertnewcustSQLresult);

                Customer_Guid = newCustomer_Guid;
                customer_id = "";
                customername = "Esop客户";
                mobile = "";
                gender = "";
            }
            //更新邮件表documentin 的 link_guid
            def updatedocinkidSQL = "update documentin set link_guid='" + linkguid + "',CUSTOMER_GUID='" + Customer_Guid + "'  Where DOCUMENTIN_GUID = '" + Temp_Guid + "' and documentto = '" + from + "'";
            def updatedocinkidresult = eliteBean.update(updatedocinkidSQL, "dataSource");
            log.debug("updatedocinkidresult:    " + updatedocinkidresult);

            String VISKEYID = "";//GD-20231027 工单前置
            int VALUE;//1 序号
            String workorderno = "";//工单编号
            // 生成相关ID
            String workorder_guid = KHUtil.getRandomString(31).toUpperCase();//工单guid
            workorder_guid = "WORK_" + workorder_guid;
            log.debug("workorder_guid:    " + workorder_guid);

            //A.-获取工单的编号 GD-202310270001
            String selectGDSQL = "select VISKEYID,VALUE from tab_getid where KEYID='GD'";
            List selectGDList = eliteBean.chaxun_sql(selectGDSQL, "dataSource");
            VISKEYID = selectGDList.get(0).get("VISKEYID");
            VALUE = selectGDList.get(0).get("VALUE");
            log.debug("返回结果VISKEYID:" + VISKEYID + "  VALUE:" + VALUE);
            VALUE = VALUE + 3;//临时按3间隙递增 后续核查看情况
            def updateGDresult = eliteBean.update("update tab_getid set VALUE='" + VALUE + "' where KEYID='GD'", "dataSource");
            log.debug("updateGDresult:" + updateGDresult);

            //对序号进线补0处理
            String paddedNumber = padNumber(VALUE);
            log.debug("paddedNumber:" + paddedNumber);
            workorderno = VISKEYID + paddedNumber;
            log.debug("workorderno:" + workorderno);

            //B.工单表  hst_workorderlist 反馈来源ID  hst_sourceId 邮件ID（按收件）
            String source = "邮件ID:" + sourceId;
            log.debug("source:" + source);

            //C. 命令组1003 hst_frame表 frame_body流程配置参数
            String selectFrameSQL = "select guid,frame_body,frame_type1,frame_type2,frame_type3,frame_name,c1.callnaturegroupdescription,c2.callnaturedescription,c3.callnature3description " +
                    " from hst_frame h" +
                    " left join callnaturegroup c1 on c1.callnaturegroup_id = h.frame_type1 " +
                    " left join callnature c2 on c2.callnature_id = h.frame_type2 " +
                    " left join callnature3 c3 on c3.callnature3_id = h.frame_type3 " +
                    " where frame_type3 ='" + workordertype3 + "' and frame_flag = 1";
            List selectFrameList = eliteBean.chaxun_sql(selectFrameSQL, "dataSource");
            def Framecount = selectFrameList.size();
            log.debug("工单流程根据业务类型三级匹配到的配置数量 Framecount：" + Framecount);
            //判断如待处理的邮件配置数量大于0则执行
            if (Framecount > 0) {
                Map FrameMap = selectFrameList.get(0);
                String frame_body = FrameMap.get("frame_body");//工单流程json
                String frame_guid = FrameMap.get("guid");//工单流程json
                log.debug("frame_body：" + frame_body + "  frame_guid:" + frame_guid);

                //解析当前流程json  获取第一个节点的信息
                JSONObject inObject = new JSONObject(frame_body);
                String nodes = GetJV(inObject, "nodes");//工单流程数组
                log.debug("  nodes:" + nodes);

                JSONArray nodesJsonArray = new JSONArray(nodes);
                int nodesInt = nodesJsonArray.length();
                log.debug("接入nodes规则:" + nodesInt);
                //获取第一个节点的信息
                JSONObject nodesJson = nodesJsonArray.getJSONObject(0);
                //获取信息
                String node = GetJV(nodesJson, "node");//工单初始第一个节点json
                JSONObject nodeObject = new JSONObject(node);
                log.debug("nodeObject:" + nodeObject);

                String nodeguid = GetJV(nodeObject, "nodeguid");//工单初始第一个节点的id
                String nodename = GetJV(nodeObject, "nodename");//工单初始第一个节点的名称
                log.debug("nodeguid:" + nodeguid + "  nodename:" + nodename);

                String audit = GetJV(nodesJson, "audit");//工单涉及处理人群 agentlist + audit_type 1/2/3
                log.debug("  audit:" + audit);
                //解析处理人 + audit_type
                JSONObject auditObject = new JSONObject(audit);
                String agent = GetJV(auditObject, "agent");
                String audit_type = GetJV(auditObject, "audit_type");//audit_type 1/2/3
                log.debug("agent:" + agent + "    audit_type:" + audit_type);

                JSONArray agentJsonArray = new JSONArray(agent);
                int agentInt = agentJsonArray.length();
                log.debug("接入agentInt:" + agentInt);

                //D. 命令组 1002 写表 hst_workorder hst_workorderlist  hst_workorder_his hst_workorderlist_his
                String[] data = new String[4];
                data[0] = "insert into hst_workorder(workorder_guid,frame_guid,frame_body,node_guid,node_name,workorderno,customer_guid,createdby,createdgroup,createdate,workorderstatus,workorder_lock,link_guid) values('" + workorder_guid + "','" + frame_guid + "','" + frame_body + "','" + nodeguid + "','" + nodename + "','" + workorderno + "','" + Customer_Guid + "','SELITE','SYSTEM',now(),'OPEN',UUID(),'" + linkguid + "')";
                data[1] = "insert into hst_workorderlist(workorder_guid,hst_customername,hst_cusgender,hst_custelephone,hst_custelephone_enc,hst_huashengid,hst_channels,hst_workordertype1,hst_workordertype2,hst_workordertype3,hst_notes,hst_sourceid,hst_source,hst_createreason) values('" + workorder_guid + "','" + customername + "','" + gender + "','***','" + mobile_enc + "','" + customer_id + "','邮件','" + workordertype1 + "','" + workordertype2 + "','" + workordertype3 + "','此工单由邮件渠道创建，详情见邮件附件！','" + sourceId + "','" + source + "','" + createreason + "')";
                data[2] = "insert into hst_workorder_his(workorder_guid,frame_guid,frame_body,node_guid,node_name,workorderno,customer_guid,createdby,createdgroup,createdate,workorderstatus,workorder_lock,link_guid,his_flag,his_date,his_guid) values('" + workorder_guid + "','" + frame_guid + "','" + frame_body + "','" + nodeguid + "','" + nodename + "','" + workorderno + "','" + Customer_Guid + "','SELITE','SYSTEM',now(),'OPEN',UUID(),'" + linkguid + "','create',now(),uuid())";
                data[3] = "insert into hst_workorderlist_his(workorder_guid,hst_customername,hst_cusgender,hst_custelephone,hst_custelephone_enc,hst_huashengid,hst_channels,hst_workordertype1,hst_workordertype2,hst_workordertype3,hst_notes,hst_sourceid,hst_source,hst_createreason,his_flag,his_date,his_guid) values('" + workorder_guid + "','" + customername + "','" + gender + "','***','" + mobile_enc + "','" + customer_id + "','邮件','" + workordertype1 + "','" + workordertype2 + "','" + workordertype3 + "','此工单由邮件渠道创建，详情见邮件附件！','" + sourceId + "','" + source + "','" + createreason + "','create',now(),uuid())";
                log.debug("data 处理SQL:" + data.toString());
                int[] dataresult = eliteBean.updateBatch(data, "dataSource");
                log.debug("data 处理SQL结果:" + dataresult.toString());

                if (audit_type == "1") {//固定审批人
                    // 命令组 1001
                    //JSON.parse(R.var.frame_table[j][1]).nodes[0].audit.audit_type==1
                    //固定审批人 循环  继续写表 hst_workorderaudit hst_workorderaudit_his
                    for (int i = 0; i < agentInt; i++) {
                        JSONObject agentJson = agentJsonArray.getJSONObject(i);
                        String audit_group = GetJV(agentJson, "audit_group");
                        String audit_agent = GetJV(agentJson, "audit_agent");
                        log.debug("audit_group:" + audit_group + "  audit_agent:" + audit_agent);

                        String[] auditdata = new String[2];
                        auditdata[0] = "insert into hst_workorderaudit(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + audit_group + "','" + audit_agent + "')";
                        auditdata[1] = "insert into hst_workorderaudit_his(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + audit_group + "','" + audit_agent + "')";
                        log.debug("auditdata 处理SQL:" + auditdata.toString());
                        int[] auditdataresult = eliteBean.updateBatch(auditdata, "dataSource");
                        log.debug("auditdata 处理SQL结果:" + auditdataresult.toString());
                    }
                } else if (audit_type == "2") {//客户对应服顾
                    // 命令组 1005
                    //JSON.parse(R.var.frame_table[j][1]).nodes[0].audit.audit_type==2
                    //对应服顾拼接 hst_workorderaudit  hst_workorderaudit_his
                    findServiceConsultant(eliteBean, workorder_guid, nodeguid, Customer_Guid);
                } else if (audit_type == "3") {//客户对应服顾领导
                    // 命令组 1006
                    //JSON.parse(R.var.frame_table[j][1]).nodes[0].audit.audit_type==3
                    //对应服顾领导拼接 hst_workorderaudit hst_workorderaudit_his
                    List selectleaderList;
                    int leaderInt = 0;
                    //查找当前客户的归属顾问
                    selectleaderList = eliteBean.chaxun_sql("select a.higher,s.defaultrolegroup,s.staffname from Authority_relationship a left join staff s on a.higher = s.staff_id where lower = (select handleby_id from objective where customer_guid = '" + Customer_Guid + "' and  map_id='MY48ZX') and lowerflag = 1 and s.workstatus = 'ONLINE'", "dataSource");
                    leaderInt = selectleaderList.size();
                    if (leaderInt == 0) {//如查询不到
                        //走1005
                        findServiceConsultant(eliteBean, workorder_guid, nodeguid, Customer_Guid);
                    }
                    for (int k = 0; k < leaderInt; k++) {
                        Map handleMap = selectleaderList.get(k);
                        String higher = handleMap.get("higher");
                        String defaultrolegroup = handleMap.get("defaultrolegroup");
                        log.debug("higher:" + higher + "    handlegroup_id:" + defaultrolegroup);
                        String[] auditdata = new String[2];
                        auditdata[0] = "insert into hst_workorderaudit(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + defaultrolegroup + "','" + higher + "')";
                        auditdata[1] = "insert into hst_workorderaudit_his(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + defaultrolegroup + "','" + higher + "')";
                        log.debug("auditdata 处理SQL:" + auditdata.toString());
                        int[] auditdataresult = eliteBean.updateBatch(auditdata, "dataSource");
                        log.debug("auditdata 处理SQL结果:" + auditdataresult.toString());
                    }
                }
                //命令组 1012 钉钉消息提醒
                String senddingmessageSQL = " insert into hst_senddingmessage (guid,message_url,workorderno,workordertype,node_name,createby,message_flag,groupids,staffids,message_type,createdate) " +
                        " select uuid(),hn.message_url,hw.workorderno,concat(c1.callnaturegroupdescription,'/',c2.callnaturedescription,'/',c3.callnature3description),hw.node_name,'SELITE',0,group_concat(distinct hr.rolegroup_id),group_concat(distinct hr.staff_id),'send',now() from hst_workorder hw " +
                        " left join hst_workorderlist hwl on hw.workorder_guid = hwl.workorder_guid " +
                        " left join hst_workorderaudit hr on hr.workorder_guid = hw.workorder_guid and hr.node_guid = hw.node_guid " +
                        " left join hst_senddingmessage_nodelist hn on hn.frame_guid = hw.frame_guid and hn.node_guid = hw.node_guid and frame_flag = 1 and (hr.rolegroup_id = hn.rolegroup_id or hn.rolegroup_id is null or hn.rolegroup_id = '') " +
                        " left join callnaturegroup c1 on c1.callnaturegroup_id = hwl.hst_workordertype1 " +
                        " left join callnature c2 on c2.callnature_id = hwl.hst_workordertype2 " +
                        " left join callnature3 c3 on c3.callnature3_id = hwl.hst_workordertype3 " +
                        " where hw.workorder_guid='" + workorder_guid + "' and hn.message_url is not null " +
                        " group by hn.message_url,hw.workorderno,concat(c1.callnaturegroupdescription,'/',c2.callnaturedescription,'/',c3.callnature3description),hw.node_name ";
                log.debug("senddingmessageSQL:" + senddingmessageSQL);
                def senddingmessageresult = eliteBean.update(senddingmessageSQL, "dataSource");
                log.debug("senddingmessageresult:" + senddingmessageresult);

            }
        } catch (Exception e) {
            log.error("创建工单异常：" + e.getMessage());
            reFlag = false;
        }

        return reFlag;
    }

    /**
     * 动态页面1005命令组 根据客户customer_guid 查找当前的顾问归属 查不到则查顾问领导
     */
    public static boolean findServiceConsultant(EliteBean eliteBean, String workorder_guid, String nodeguid, String Customer_Guid) {
        boolean reFlag = true;

        List selecthandleList;
        int handleInt = 0;
        try {
            //查找当前客户的归属顾问
            selecthandleList = eliteBean.chaxun_sql("select handleby_id,handlegroup_id,s.staffname from objective o left join staff s on s.staff_id = o.handleby_id  where 1=1  and customer_guid = '" + Customer_Guid + "' and  map_id='MY48ZX' and s.workstatus = 'ONLINE'", "dataSource");
            handleInt = selecthandleList.size();

            if (handleInt == 0) {//如查询不到，则查询系统参数配置的顾问主管 可能存在多个人
                selecthandleList = eliteBean.chaxun_sql("select t1.parametervalue as handleby_id,t2.parametervalue as handlegroup_id,s.staffname from " +
                        " (select parametervalue from sysc_parameter where parameter_id = 'DEFFGS') t1 left join staff s on parametervalue = s.staff_id, " +
                        " (select parametervalue from sysc_parameter where parameter_id = 'DEFFGR') t2", "dataSource");
                handleInt = selecthandleList.size();
            }

            for (int j = 0; j < handleInt; j++) {
                Map handleMap = selecthandleList.get(j);
                String handleby_id = handleMap.get("handleby_id");
                String handlegroup_id = handleMap.get("handlegroup_id");
                String staffname = handleMap.get("staffname");
                log.debug("handleby_id:" + handleby_id + "    handlegroup_id:" + handlegroup_id + "   staffname:" + staffname);
                String[] auditdata = new String[2];
                auditdata[0] = "insert into hst_workorderaudit(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + handlegroup_id + "','" + handleby_id + "')";
                auditdata[1] = "insert into hst_workorderaudit_his(workorder_guid,node_guid,rolegroup_id,staff_id) values('" + workorder_guid + "','" + nodeguid + "','" + handlegroup_id + "','" + handleby_id + "')";
                log.debug("auditdata 处理SQL:" + auditdata.toString());
                int[] auditdataresult = eliteBean.updateBatch(auditdata, "dataSource");
                log.debug("auditdata 处理SQL结果:" + auditdataresult.toString());
            }
        } catch (Exception e) {
            log.error("1005异常:" + e.getMessage());
            reFlag = false;
        }

        return reFlag;
    }

    /**
     * 去除邮件正文内的HTM+CSSL的标签
     * @param htmlStr 邮件内容
     * @return htmlStr 邮件内容
     */
    public static String delHTMLTag(String htmlStr) {
        String regEx_style = "<style[^>]*?>[\\s\\S]*?<\\/style>"; //定义style的正则表达式
        String regEx_html = "<[^>]+>"; //定义HTML标签的正则表达式
        Pattern p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
        Matcher m_style = p_style.matcher(htmlStr);
        htmlStr = m_style.replaceAll(""); //过滤style标签
        Pattern p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
        Matcher m_html = p_html.matcher(htmlStr);
        htmlStr = m_html.replaceAll(""); //过滤html标签
        htmlStr = htmlStr.replace(" ", "");
        htmlStr = htmlStr.replaceAll("\\s*|\t|\r|\n", "");
        htmlStr = htmlStr.replace("“", "");
        htmlStr = htmlStr.replace("”", "");
        htmlStr = htmlStr.replaceAll("　", "");

        return htmlStr.trim(); //返回文本字符串
    }

    // 解析Json值
    public static String GetJV(JSONObject inJson, String getKey) {
        String Rev_Value = null;
        try {
            Rev_Value = inJson.getString(getKey);
        } catch (Exception e) {
            try {
                Rev_Value = String.valueOf(inJson.getInt(getKey));
            } catch (Exception e1) {
                try {
                    Rev_Value = String.valueOf(inJson.getJSONArray(getKey));
                } catch (Exception e2) {
                    try {
                        Rev_Value = String.valueOf(inJson.getJSONObject(getKey));
                    } catch (Exception e3) {
                        Rev_Value = "";
                    }
                }
            }
        } finally {
            if (Rev_Value == null || Rev_Value.equals("null") || Rev_Value.length() <= 0) {
                Rev_Value = "";
            } else {
            }
        }
        //log.debug("解析-k=" + getKey + " v=" + Rev_Value);
        return Rev_Value;
    }

    // 工单编号后四位序号补0
    public static String padNumber(int number) {
        String str = String.valueOf(number);

        if (str.length() < 4) {
            while (str.length() < 4) {
                str = "0".concat(str);
            }
        }
        return str;
    }

}
